Merge Replication (Again)

[niben]

Hi All,

I have posted this earlier and I am re-posting it simplifying what I had said.

The scenario is:

I have two sql server database instances with the same database schema and all. However, both of them have different data. I have not set them for replication at all. Now, I want to do merge replication between them such that the data between them could be syncronized.

When I do pull merge subscription I have two choices -
1.Bringing schema and data to subscriber from publisher

2.Not bringing the schema and data from publisher to subscriber.

Obviously, I chose the second choice. But upon syncronizing I dont see any data from publisher coming to subscriber and vice-versa. If I add new data to publisher and do syncronization, I can see ONLY the new data created after the replication setup in subscriber. If I add new data to subscriber and do syncronization then the new data is removed from subscriber and not propagated to publisher.

Is there any way I can make this work??

Niben

[Stephen]

Replication is doing exactly what you asked - do not bring schema and data.

What you really want is get replication to get the data from server 1 (currently) and add it to server 2 and to get the data that is currently on server 2 to server 1.

To achieve this, I think you will need to either do this yourself or get replication to do it. Before doing this, you must make sure that there will be no clashes with primary keys/constraints between the data on both servers.

If you set up a publication that has a horizontal filter that matches the data on one of the servers, you will be able to get replication to manage the movement of the data from one server to the other which will get them in sync with each other. Do the equivalent but in the other direction and you will end up with servers that have the same data on them.

You should check what publication will do when delivering the initial data. By default you may well find that the destination table will be dropped. You should ensure that each article in the publication is set to either delete data that matches the row filter statement or to keep the existing table unchanged. Otherwise you will end up trashing a whole bunch of data you really wanted to keep.

When both subscriptions have gottent the data to the subscriber, you can then think about using a single publication that has no filter. This would be simpler to manage.

And the caveat is "You should test this out using non-production data first".

Cheers
Stephen

[niben]

Stephen,

So do you mean to say - when I set up merge publication with horizontal filters that matches data from the server and I choose to do pull subscription with an option of not bringing schema and data, all the data from publisher will be transferred to subscriber and vice-versa?

I guess, if I choose to do pull subscription by bringing data and schema to subscriber then the existing data in subscriber will be overwritten before it is replicated to the publisher.

I am quite confused and I will appreciate your few words in it.

Hope to hear from you soon.

Niben

[Stephen]

No this is not what I am saying at all. If you say that you do not want replication to send data and schema this is exactly what it will do - i.e. if will not send any data which is what you are so concerned about in the first place.

You are confused about options you define when the publication is created versus options for the subscription to that publication.

If replication is told to deliver the schema, by default, each table in the publication will be dropped and created. You will not want to do this if you need to keep the data currently in each database.

You need to have a look at the article defaults when you are creating the publication. This is in the definition of the publication NOT the subscription.

I assume that you are using Enterprise Manager to do this. When you are selecting the tables that are in the publication you will see a button called "Article Defaults". Click on this and you will see a tab called "Snapshot". Have a look at this tab.

[niben]

...so it seems like there is no good way of doing syncronization in sql server for the data that existed before publication-subscription set up.

Stephen, by carefully following what you said, essentially we are just trying to flat transfer data between publisher and subscriber. By flat transfer I mean we are just importing data from one computer to another and vice-versa. And, hence we even need to check for primary key violation and such.

Is it possible to actually do a syncronization between the data that existed from before replication setup in sql server?

For instance:

Computer A has a table User:

ID Name Address
-- ---- -------
1 Elle Minnesota
2 Matt Kansas
3 Tim Miami
4 Jim St.Paul

Computer B has a table User:

ID Name Address
-- ---- -------
2 Niben Nepal
5 Kim Miami
6 Doug St.Paul

Now when we set up A as a publisher for merge replication and B as a pull subscriber.

After doing that can we have a syncronization of data between the computers as (if ID 2 in Computer B wins the conflict):

Computer A's User table:

ID Name Address
-- ---- -------
1 Elle Minnesota
2 Niben Nepal
3 Tim Miami
4 Jim St.Paul
5 Kim Miami
6 Doug St.Paul

Computer B's User table:

ID Name Address
-- ---- -------
1 Elle Minnesota
2 Niben Nepal
3 Tim Miami
4 Jim St.Paul
5 Kim Miami
6 Doug St.Paul

Is it even possible??

[Stephen]

1. If you have used the same primary keys on both servers but for different data, you have a significant problem that you must resolve yourself. Replication is not a miracle worker.

2. Replication will copy the data for you. You simply need to following the outline I sent previously.

I fail to understand where your issue is. Replication will get the job done exactly as you want it to.

[niben]

Primary Key Issue is not really an issue for me. I can use GUID or cluster of columns as primary keys to resolve the issue. So, if there is any row with existing primary keys in subscriber (or publisher) then this will really need to be resolved and it is ok to do a conflict resolution as SQL Server normally does.

Stephen, yes I have already tried your way with different approaches and it does work as you said. Replication will bring the data.

I just want SQL Server to do syncronization to the data existed before replication setup in the same way as it handles with the data after the setting up publisher and subscriber...thats all.

With your way sql server is just filling in data by importing from remote publisher or subscriber and this will be a problem for me.

Here's a brief scenario how the Primary Key conflict will occur (I am sorry for not giving good example before):
-----------------------------------

There are three computers A, B and C with Table User and rows given below:


User table for A:

ID Name Address
-- ---- -------
1 Elle Minnesota
2 Matt Kansas
3 Tim Miami
4 Jim St.Paul


User table for B:

ID Name Address
-- ---- -------
5 Kim Miami
6 Doug St.Paul
7 Niben Nepal

User table for C:

ID Name Address
-- ---- -------
8 Jo LA
9 Ron CA
10 Don PA

** We need to totally syncronize A,B,C with each other, and I want to follow the following process to do that (this is just a simple example btw)

1. A acts as a publisher and B,C will subscribe to A. After replication here's what A,B,C will be (assuming B does the replication before C):

User table for A:

ID Name Address
-- ---- -------
1 Elle Minnesota
2 Matt Kansas
3 Tim Miami
4 Jim St.Paul
5 Kim Miami
6 Doug St.Paul
7 Niben Nepal
8 Jo LA
9 Ron CA
10 Don PA


User table for B:

ID Name Address
-- ---- -------
1 Elle Minnesota
2 Matt Kansas
3 Tim Miami
4 Jim St.Paul
5 Kim Miami
6 Doug St.Paul
7 Niben Nepal

User table for C:

ID Name Address
-- ---- -------
1 Elle Minnesota
2 Matt Kansas
3 Tim Miami
4 Jim St.Paul
8 Jo LA
9 Ron CA
10 Don PA

2. Now to syncronize B and C, I will have B as a publisher and C as a subscriber (or vice-versa) and do syncronization (This time there will be conflicts with p-keys) and the result should be:


User table for B:

ID Name Address
-- ---- -------
1 Elle Minnesota
2 Matt Kansas
3 Tim Miami
4 Jim St.Paul
5 Kim Miami
6 Doug St.Paul
7 Niben Nepal
8 Jo LA
9 Ron CA
10 Don PA

User table for C:

ID Name Address
-- ---- -------
1 Elle Minnesota
2 Matt Kansas
3 Tim Miami
4 Jim St.Paul
5 Kim Miami
6 Doug St.Paul
7 Niben Nepal
8 Jo LA
9 Ron CA
10 Don PA

Thats all I was looking for.

Thanks.

[Stephen]

Have a look at using the "@creation_script" option for each article. If you specify this, replication will use this script to create the table. If the script doesn't actually do anything (e.g. Print 'Got here') that will ensure that the existing table is not dropped.

For the article snapshot properties, specify "Drop the existing table and -re-create it". This should then use you script to drop/create the table and then deliver the snapshot of the data.

Since the table is not dropped, this will have the effect of getting the data from one server to the other.

In preparation for this, you will need to add a uniqueidentifier column to each table in the publication.

[niben]

...so you're saying if I specify a script name as the parameter for @creation_script in @sp_addmergearticle stored procedure used to create publisher then sql server will use that script to re-create the table in subscriber, and I can do anything in the custom script. You are suggesting to write the script such that it delivers the contents of publisher to subscriber and vice-versa, right?

Right now I have no idea how to deliver contents of a table in Publisher to Subscriber and vice-versa. I will surely look into it. Please let me know if you do know how to do that. It will help me a lot.

Thanks Stephen.

Niben

[Stephen]

I think you should be doing some reading about replication and what each of the stored procs involved is actually doing. sp_addmergearticle does not create the publisher. It is used to add an article to an existing publication. Before a publication may be created, you need to configure the server to be a publisher (and possibly a distributor).

Most of the basic functions of replication are handled for you if you use the wizards in EM. However, what you are doing is not really covered by these wizards.

The creation script is used to specify the SQL for creating the table on the subscriber for an article. If you specify this, you will have control over what replication actually does when it decides that a table needs to be created. Since you do not want replication to drop/create the table (article), you would specify the name of a script that does nothing (e.g. Select 'Got here'). If you don't specify this parameter, replication will generate a script that contains a CREATE TABLE command for each article.

If you use the @pre_creation_cmd to either truncate or delete data that satisfies the Where clause for the range of keys that are on each of the relevant servers (this would be different depending on the server for you).

[niben]

>I think you should be doing some reading about replication and what each of the stored procs involved is actually doing.
I am sorry I should be saying sp_addmergearticle adds articles to publication. Actually, I am using my custom stored procedure to create distributor, publication and subscription.

Thanks for the pointers, I will try it and see.

Stephen, thanks a lot for your help.

Niben